package com.zhangwoo.util;

import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

/**
 * 基本DAO操作。
 * 
 * @author chenc@inetcop.com.cn
 * @date 2009-04-22
 * 
 */
public class BaseDao {
	
	private Logger logger=Logger.getLogger(BaseDao.class);
	
	public BaseDao(){}	
	/**
	 * 更新或保存语句执行，此类语句一定有参数
	 * @param sqlStr
	 * @param parasValue
	 * @return 影响行数
	 * @throws SQLException 
	 * @throws SQLException 
	 */
	public synchronized void save(String sqlStr,Object[] parasValue) throws SQLException {
		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		PreparedStatement basePsm = null;
		try {
			baseCon.setAutoCommit(false);
			basePsm=baseCon.prepareStatement(sqlStr);
			logger.debug("execute save sql : '"+sqlStr+"' ");
			for(int i=0;i<parasValue.length;i++){
				basePsm.setObject(i+1, parasValue[i]);
			}
			basePsm.executeUpdate();
			baseCon.commit();
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}
		}
	}
	
	/**
	 * 无参数保存，使用statement
	 * @param sqlStr
	 * @return
	 * @throws SQLException
	 */
	public void save(String sqlStr) throws SQLException {		
		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		Statement basePsm = baseCon.createStatement();
		ResultSet baseResult=null;
		try {
			baseCon.setAutoCommit(false);
			basePsm.executeUpdate(sqlStr);		
			baseCon.commit();
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}

	/**
	 * 调用存储过程无参数保存，使用statement
	 * @param sqlStr
	 * @return
	 * @throws SQLException
	 */
	public void call(String sqlStr) throws SQLException {		
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		CallableStatement basePsm = baseCon.prepareCall(sqlStr);
		ResultSet baseResult=null;
		try {
			basePsm.execute();		
		} catch (SQLException e) {
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}
	
	/**
	 * 传入一个对象数组的数组，以实现批量更新
	 * @param sqlStr
	 * @param parasValues
	 * @throws SQLException
	 */
	public synchronized void save(String sqlStr,List<Object[]> parasValues) throws SQLException{
		logger.info("execute sql : '"+sqlStr+"' ");
//		if(baseParams.containsKey(sqlStr)){
//			baseParams.get(sqlStr).addAll(parasValues);
//		}else{
//			baseParams.put(sqlStr,parasValues);
//		}
//		
//		// 若参数列表达到指定批处理数量才处理，否则跳出
//		if(baseParams.get(sqlStr).size()<batchSize)
//			return;
		logger.info("execute batch save sql : '"+sqlStr+"' with params "+parasValues.size());
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		baseCon.setAutoCommit(true);
		PreparedStatement basePsm = null;
		try {
			basePsm=baseCon.prepareStatement(sqlStr);
			for(int i=0;i<parasValues.size();i++){
				Object[] parasValue=parasValues.get(i);				
				for(int j=0;j<parasValue.length;j++){
					basePsm.setObject(j+1, parasValue[j]);
				}
				basePsm.addBatch();
			}
			try {
				basePsm.executeBatch();
//				baseCon.commit();
			}catch (BatchUpdateException e) {
				throw e;
			}
//			baseParams.remove(sqlStr);
		} catch (SQLException e) {
			throw e;
		} finally {
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}
		}
	}
	/**
	 * 去synchronized
	 * 传入一个对象数组的数组，以实现批量更新
	 * @param sqlStr
	 * @param parasValues
	 * @throws SQLException
	 */
	public void save2(String sqlStr,List<Object[]> parasValues) throws SQLException{
		logger.debug("execute batch save sql : '"+sqlStr+"' with params "+parasValues.size());
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		baseCon.setAutoCommit(false);
		PreparedStatement basePsm = null;
		try {
			basePsm=baseCon.prepareStatement(sqlStr);
			for(int i=0;i<parasValues.size();i++){
				Object[] parasValue=parasValues.get(i);				
				for(int j=0;j<parasValue.length;j++){
					basePsm.setObject(j+1, parasValue[j]);
				}
				basePsm.addBatch();
				
				if((i>0 && i%100000 == 0) || i==parasValues.size()-1){//逢10w提交一次
					try {
						basePsm.executeBatch();
						baseCon.commit();
						basePsm.clearBatch();
						logger.debug("====="+i);
					}catch (BatchUpdateException e) {
//						baseCon.setAutoCommit(true);
						if(e.getMessage().indexOf("IX_history_realtime_day")!=-1){
							//logger.error("批量插入错误："+e.getMessage(),e);
							continue;
						}
						throw e;
					}
				}
			}
			
		} catch (SQLException e) {
			baseCon.setAutoCommit(true);
			throw e;
		} finally {
			baseCon.setAutoCommit(true);
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}
		}
	}
	
	/**
	 * 有参数查询，尽量使用投影查询，以便确定字段位置。
	 * @param sqlStr
	 * @param parasValue
	 * @return 获取结果数组
	 * @throws SQLException
	 */
	public synchronized List<Object[]> query(String sqlStr,Object[] parasValue) throws SQLException{
//		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		PreparedStatement basePsm = null;
		ResultSet baseResult=null;
		try {
			baseCon.setAutoCommit(false);
//			this.basePsm=this.baseCon.prepareStatement(sqlStr,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);// 允许结果集前后滚动
			basePsm=baseCon.prepareStatement(sqlStr);
			for(int i=0;i<parasValue.length;i++){
				basePsm.setObject(i+1, parasValue[i]);
			}
//			logger.debug("execute query sql : '"+sqlStr+"' ");
			baseResult=basePsm.executeQuery();		
			baseCon.commit();
//			this.baseResult.last();
//			logger.debug("get size "+this.baseResult.getRow());
//			this.baseResult.beforeFirst();			
			ResultSetMetaData rmd=baseResult.getMetaData();
			List<Object[]> results=new ArrayList<Object[]>();
			for(;baseResult.next();){
				Object[] result=new Object[rmd.getColumnCount()];
				for(int i=0;i<rmd.getColumnCount();i++){
					result[i]=baseResult.getObject(i+1);
				}				
				results.add(result);
			}
			return results;
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}
	
	/**
	 * 无参数查询，使用statement
	 * @param sqlStr
	 * @return
	 * @throws SQLException
	 */
	public synchronized List<Object[]> query(String sqlStr) throws SQLException{
//		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		Statement basePsm = baseCon.createStatement();
		ResultSet baseResult=null;
		try {
			baseCon.setAutoCommit(false);
			baseResult=basePsm.executeQuery(sqlStr);		
			baseCon.commit();		
			ResultSetMetaData rmd=baseResult.getMetaData();
			List<Object[]> results=new ArrayList<Object[]>();
			for(;baseResult.next();){
				Object[] result=new Object[rmd.getColumnCount()];
				for(int i=0;i<rmd.getColumnCount();i++){
					result[i]=baseResult.getObject(i+1);
				}				
				results.add(result);
			}
			return results;
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}	
	/**
	 * 去synchronized 
	 * 无参数查询，使用statement
	 * @param sqlStr
	 * @return
	 * @throws SQLException
	 */
	public List<Object[]> query2(String sqlStr) throws SQLException{
//		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		Statement basePsm = baseCon.createStatement();
		ResultSet baseResult=null;
		try {
			baseCon.setAutoCommit(false);
			baseResult=basePsm.executeQuery(sqlStr);		
			baseCon.commit();		
			ResultSetMetaData rmd=baseResult.getMetaData();
			List<Object[]> results=new ArrayList<Object[]>();
			for(;baseResult.next();){
				Object[] result=new Object[rmd.getColumnCount()];
				for(int i=0;i<rmd.getColumnCount();i++){
					result[i]=baseResult.getObject(i+1);
				}				
				results.add(result);
			}
			return results;
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}	
	
	/**
	 * 特殊的无参数查询（List中首个数组保存所有的栏位名词），使用statement
	 * @param sqlStr
	 * @return
	 * @throws SQLException
	 */
	public synchronized List<Object[]> query(String sqlStr,boolean cols) throws SQLException{
//		logger.debug("execute sql : '"+sqlStr+"' ");
		Connection baseCon = ConnectionsPool.getInstance().getConnection();
		Statement basePsm = baseCon.createStatement();
		ResultSet baseResult=null;
		try {
			baseCon.setAutoCommit(false);
			baseResult=basePsm.executeQuery(sqlStr);		
			baseCon.commit();		
			ResultSetMetaData rmd=baseResult.getMetaData();
			List<Object[]> results=new ArrayList<Object[]>();
			
			// 获取栏位名词
			Object[] col=new Object[rmd.getColumnCount()];
			for(int   i=0;   i <rmd.getColumnCount();   i++)   {
				col[i]=rmd.getColumnName(i+1);
			}
			results.add(col);
			
			for(;baseResult.next();){
				Object[] result=new Object[rmd.getColumnCount()];
				for(int i=0;i<rmd.getColumnCount();i++){
					result[i]=baseResult.getObject(i+1);
				}				
				results.add(result);
			}
			return results;
		} catch (SQLException e) {
			baseCon.rollback();
			throw e;
		} finally {
			if(baseResult!=null){
				baseResult.close();
				baseResult=null;
			}
			if(basePsm!=null){
				basePsm.close();
				basePsm=null;
			}
			if(baseCon!=null){
				ConnectionsPool.getInstance().giveBackConnectioni(baseCon);
			}		
		}
	}	
	
	public static void main(String[] args) {
		try {
			new BaseDao().save("insert kvs (hid,hkeys,hvalues) values (1,'我 and 你','我命中你了')");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
